package com.cloudweb.oa.service.impl;

import cn.js.fan.db.ListResult;
import cn.js.fan.db.ResultIterator;
import cn.js.fan.db.ResultRecord;
import cn.js.fan.util.*;
import com.alibaba.fastjson.JSONArray;
import com.alibaba.fastjson.JSONObject;
import com.cloudweb.oa.security.AuthUtil;
import com.cloudweb.oa.service.WorkflowStatService;
import com.cloudweb.oa.utils.I18nUtil;
import com.cloudweb.oa.vo.Result;
import com.cloudwebsoft.framework.db.JdbcTemplate;
import com.cloudwebsoft.framework.util.LogUtil;
import com.redmoon.oa.dept.DeptDb;
import com.redmoon.oa.dept.DeptUserDb;
import com.redmoon.oa.person.UserDb;
import com.redmoon.oa.pvg.Privilege;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import org.springframework.web.bind.annotation.RequestParam;

import javax.servlet.http.HttpServletRequest;
import java.sql.SQLException;
import java.util.Calendar;
import java.util.Iterator;
import java.util.Vector;

@Service
public class WorkflowStatServiceImpl implements WorkflowStatService {

    @Autowired
    HttpServletRequest request;

    @Autowired
    AuthUtil authUtil;

    @Autowired
    I18nUtil i18nUtil;

    @Override
    public JSONObject listByDept(@RequestParam(value="deptCode") String deptCode, @RequestParam(value="op", required = false) String op, @RequestParam(value="op", required = false) String action, Integer page, Integer pageSize) throws ErrMsgException {
        com.alibaba.fastjson.JSONObject json = new com.alibaba.fastjson.JSONObject();
        com.redmoon.oa.dept.DeptDb dd = new com.redmoon.oa.dept.DeptDb();
        dd = dd.getDeptDb(deptCode);
        if (dd==null || !dd.isLoaded()) {
            throw new ErrMsgException("部门" + deptCode + "不存在！");
        }

        DeptDb deptDb = new DeptDb();
        deptDb = deptDb.getDeptDb(deptCode);
        Vector dv = new Vector();
        deptDb.getAllChild(dv, deptDb);
        String depts = StrUtil.sqlstr(deptCode);
        for (Object value : dv) {
            deptDb = (DeptDb) value;
            depts += "," + StrUtil.sqlstr(deptDb.getCode());
        }

        DeptUserDb jd = new DeptUserDb();
        UserDb ud = new UserDb();

        Calendar cal = Calendar.getInstance();
        Calendar cal2 = Calendar.getInstance();
        String rename = ParamUtil.get(request,"rename");
        int cury = cal.get(Calendar.YEAR);
        int y = ParamUtil.getInt(request, "showyear", cury);

        String sqlUser = "select du.ID from dept_user du, users u where du.user_name=u.name and u.isValid=1 and du.DEPT_CODE in (" + depts + ")";
        if ("search".equals(op)) {
            sqlUser += " and u.realName like '%" + rename + "%'";
        }
        sqlUser += " order by du.DEPT_CODE asc, du.orders asc";

        long total = 0;
        Vector v;
        if ("export".equals(action)) {
            v = jd.listBySQL(sqlUser);
        } else {
            ListResult lr = jd.listResult(sqlUser, page, pageSize);
            v = lr.getResult();
            total = lr.getTotal();
        }
        String realName;

        JSONArray ary = new JSONArray();

        JdbcTemplate jt = new JdbcTemplate();
        jt.setAutoClose(false);
        try {
            for (Object o : v) {
                DeptUserDb pu = (DeptUserDb) o;

                if (!"".equals(pu.getUserName())) {
                    ud = ud.getUserDb(pu.getUserName());
                }
                realName = ud.getRealName();

                dd = deptDb.getDeptDb(pu.getDeptCode());

                String sql = "select sum(performance) from flow_my_action where performance<>0 and (user_name=" + StrUtil.sqlstr(pu.getUserName()) + " or proxy=" + StrUtil.sqlstr(pu.getUserName()) + ") and is_checked=1 and checker=? and receive_date>=? and receive_date<=?";
                // 全年绩效
                double yPerf = 0.0;

                JSONObject row = new JSONObject();
                row.put("userName", pu.getUserName());
                row.put("realName", realName);
                row.put("deptName", dd.getName());

                for (int i = 0; i <= 11; i++) {
                    int d = DateUtil.getDayCount(y, i);
                    cal.set(y, i, 1, 0, 0, 0);
                    cal2.set(y, i, d, 23, 59, 59);
                    ResultIterator ri = jt.executeQuery(sql, new Object[]{pu.getUserName(), cal.getTime(), cal2.getTime()});
                    double perf = 0.0;
                    if (ri.hasNext()) {
                        ResultRecord rr = (ResultRecord) ri.next();
                        perf = rr.getDouble(1);
                    }
                    yPerf += perf;
                    row.put(String.valueOf(i + 1), NumberUtil.round(perf, 2));
                }
                row.put("amount", NumberUtil.round(yPerf, 2));
                ary.add(row);
            }
        } catch (SQLException e) {
            LogUtil.getLog(getClass()).error(e);
        } finally {
            jt.close();
        }

        json.put("total", total);
        json.put("list", ary);
        return json;
    }

    @Override
    public JSONObject listCountByDept(String deptCode, String op, String action, Integer page, Integer pageSize) throws ErrMsgException {
        com.redmoon.oa.dept.DeptDb dd = new com.redmoon.oa.dept.DeptDb();
        dd = dd.getDeptDb(deptCode);
        if (dd == null || !dd.isLoaded()) {
            throw new ErrMsgException("部门" + deptCode + "不存在！");
        }

        Privilege privilege = new Privilege();
        String myname = ParamUtil.get(request, "userName");
        if("".equals(myname)){
            myname = privilege.getUser(request);
        }
        if (!myname.equals(privilege.getUser(request))) {
            if (!(privilege.canAdminUser(request, myname))) {
                throw new ErrMsgException(i18nUtil.get("pvg_invalid"));
            }
        }

        com.alibaba.fastjson.JSONObject json = new com.alibaba.fastjson.JSONObject();
        Calendar cal = Calendar.getInstance();
        Calendar cal2 = Calendar.getInstance();
        String rename = ParamUtil.get(request,"rename");
        int cury = cal.get(Calendar.YEAR);
        int y = ParamUtil.getInt(request, "showYear", cury);

        DeptDb deptDb = new DeptDb();
        deptDb = deptDb.getDeptDb(deptCode);
        Vector dv = new Vector();
        deptDb.getAllChild(dv, deptDb);
        String depts = StrUtil.sqlstr(deptCode);
        Iterator ird = dv.iterator();
        while (ird.hasNext()) {
            deptDb = (DeptDb)ird.next();
            depts += "," + StrUtil.sqlstr(deptDb.getCode());
        }

        DeptUserDb jd = new DeptUserDb();
        UserDb ud = new UserDb();

        String sql_user = "select du.ID from dept_user du, users u where du.user_name=u.name and u.isValid=1 and du.DEPT_CODE in (" + depts + ")";
        if ("search".equals(op)) {
            sql_user += " and u.realName like '%" + rename+"%'";
        }
        sql_user +="order by du.DEPT_CODE asc, du.orders asc";

        Vector v;
        long total = 0;
        if ("export".equals(action)) {
            v = jd.listBySQL(sql_user);
        } else {
            ListResult lr = jd.listResult(sql_user, page, pageSize);
            v = lr.getResult();
            total = lr.getTotal();
        }

        String realName = "";
        JSONArray list = new JSONArray();
        JdbcTemplate jt = new JdbcTemplate();
        jt.setAutoClose(false);
        try {
            for (Object o : v) {
                DeptUserDb pu = (DeptUserDb) o;
                String user_name = pu.getUserName();
                ud = ud.getUserDb(pu.getUserName());
                realName = ud.getRealName();
                dd = deptDb.getDeptDb(pu.getDeptCode());

                JSONObject row = new JSONObject();
                String sql = "select sum(performance) from flow_my_action where performance<>0 and (user_name=" + StrUtil.sqlstr(user_name) + " or proxy=" + StrUtil.sqlstr(user_name) + ") and is_checked=1 and checker=? and receive_date>=? and receive_date<=?";
                String sql2 = "select count(*) from flow_my_action where performance<>0 and (user_name=" + StrUtil.sqlstr(user_name) + " or proxy=" + StrUtil.sqlstr(user_name) + ") and is_checked=1 and checker=? and receive_date>=? and receive_date<=?";
                double yPerf = 0.0;
                double yCount = 0;
                double yAverage = 0;
                for (int i=0; i<=11; i++) {
                    int d = DateUtil.getDayCount(y, i);
                    cal.set(y, i, 1, 0, 0, 0);
                    cal2.set(y, i, d, 23, 59, 59);
                    ResultIterator ri = jt.executeQuery(sql, new Object[]{user_name, cal.getTime(), cal2.getTime()});
                    double perf = 0.0;
                    if (ri.hasNext()) {
                        ResultRecord rr = ri.next();
                        perf = rr.getDouble(1);
                    }
                    yPerf += perf;
                    int count = 0;
                    ri = jt.executeQuery(sql2, new Object[]{user_name, cal.getTime(), cal2.getTime()});
                    if (ri.hasNext()) {
                        ResultRecord rr = ri.next();
                        count = rr.getInt(1);
                    }
                    yCount += count;

                    row.put(String.valueOf(i+1), count);
                }
                row.put("amount", yCount);
                row.put("realName", realName);
                row.put("deptName", dd.getName());
                list.add(row);
            }
        } catch (SQLException e) {
            LogUtil.getLog(getClass()).error(e);
        } finally {
            jt.close();
        }
        json.put("list", list);
        json.put("total", total);
        return json;
    }

    @Override
    public JSONObject listAverageByDept(String deptCode, String op, String action, Integer page, Integer pageSize) throws ErrMsgException {
        com.redmoon.oa.dept.DeptDb dd = new com.redmoon.oa.dept.DeptDb();
        dd = dd.getDeptDb(deptCode);
        if (dd == null || !dd.isLoaded()) {
            throw new ErrMsgException("部门" + deptCode + "不存在！");
        }

        Privilege privilege = new Privilege();
        String myname = ParamUtil.get(request, "userName");
        if("".equals(myname)){
            myname = privilege.getUser(request);
        }
        if (!myname.equals(privilege.getUser(request))) {
            if (!(privilege.canAdminUser(request, myname))) {
                throw new ErrMsgException(i18nUtil.get("pvg_invalid"));
            }
        }

        com.alibaba.fastjson.JSONObject json = new com.alibaba.fastjson.JSONObject();
        Calendar cal = Calendar.getInstance();
        Calendar cal2 = Calendar.getInstance();
        String rename = ParamUtil.get(request,"rename");
        int cury = cal.get(Calendar.YEAR);
        int y = ParamUtil.getInt(request, "showYear", cury);

        DeptDb deptDb = new DeptDb();
        deptDb = deptDb.getDeptDb(deptCode);
        Vector dv = new Vector();
        deptDb.getAllChild(dv, deptDb);
        String depts = StrUtil.sqlstr(deptCode);
        Iterator ird = dv.iterator();
        while (ird.hasNext()) {
            deptDb = (DeptDb)ird.next();
            depts += "," + StrUtil.sqlstr(deptDb.getCode());
        }

        DeptUserDb jd = new DeptUserDb();
        UserDb ud = new UserDb();

        String sql_user = "select du.ID from dept_user du, users u where du.user_name=u.name and u.isValid=1 and du.DEPT_CODE in (" + depts + ")";
        if ("search".equals(op)) {
            sql_user += " and u.realName like '%" + rename+"%'";
        }
        sql_user +="order by du.DEPT_CODE asc, du.orders asc";

        Vector v;
        long total = 0;
        if ("export".equals(action)) {
            v = jd.listBySQL(sql_user);
        } else {
            ListResult lr = jd.listResult(sql_user, page, pageSize);
            v = lr.getResult();
            total = lr.getTotal();
        }

        String realName = "";
        JSONArray list = new JSONArray();
        JdbcTemplate jt = new JdbcTemplate();
        jt.setAutoClose(false);
        try {
            for (Object o : v) {
                DeptUserDb pu = (DeptUserDb) o;
                String user_name = pu.getUserName();
                ud = ud.getUserDb(pu.getUserName());
                realName = ud.getRealName();
                dd = deptDb.getDeptDb(pu.getDeptCode());

                JSONObject row = new JSONObject();
                String sql = "select sum(performance) from flow_my_action where performance<>0 and (user_name=" + StrUtil.sqlstr(user_name) + " or proxy=" + StrUtil.sqlstr(user_name) + ") and is_checked=1 and checker=? and receive_date>=? and receive_date<=?";
                String sql2 = "select count(*) from flow_my_action where performance<>0 and (user_name=" + StrUtil.sqlstr(user_name) + " or proxy=" + StrUtil.sqlstr(user_name) + ") and is_checked=1 and checker=? and receive_date>=? and receive_date<=?";
                double yPerf = 0.0;
                double yCount = 0;
                double yAverage = 0;
                for (int i=0; i<=11; i++) {
                    int d = DateUtil.getDayCount(y, i);
                    cal.set(y,i,1,0,0,0);
                    cal2.set(y,i,d,23,59,59);
                    ResultIterator ri = jt.executeQuery(sql, new Object[]{user_name, cal.getTime(), cal2.getTime()});
                    double perf = 0.0;
                    if (ri.hasNext()) {
                        ResultRecord rr = ri.next();
                        perf = rr.getDouble(1);
                    }
                    yPerf += perf;
                    int count = 0;
                    ri = jt.executeQuery(sql2, new Object[]{user_name, cal.getTime(), cal2.getTime()});
                    if (ri.hasNext()) {
                        ResultRecord rr = ri.next();
                        count = rr.getInt(1);
                    }
                    yCount += count;
                    double average = 0;
                    if (count>0) {
                        average = perf/count;
                    }

                    row.put(String.valueOf(i+1), average);
                }
                if (yCount>0) {
                    yAverage = yPerf/yCount;
                }
                row.put("amount", NumberUtil.round(yAverage, 2));
                row.put("realName", realName);
                row.put("deptName", dd.getName());
                list.add(row);
            }
        } catch (SQLException e) {
            LogUtil.getLog(getClass()).error(e);
        } finally {
            jt.close();
        }
        json.put("list", list);
        json.put("total", total);
        return json;
    }
}
